L银行跑批任务包含很多存储过程，其中“对公贷款协议明细”存储过程运行时间2个小时，是整个跑批任务的瓶颈，亟需优化。 从这个存储过程运行日志可以看出，对公贷款协议明细共有48个步骤，每个步骤包括几个主要SQL和一些辅助SQL组成，共有3300行。从下图可以看出各个步骤执行的时间长度：
首先，深入了解数据和计算特征。 对公贷款协议明细存储过程的48个步骤比较复杂，但每个步骤基本上都是三部曲：1、执行主要SQL；2、结果保存为临时表；3、为临时表建索引。执行主要SQL的时候，一般会用到前序步骤生成的临时表。 我们以耗时最长的步骤7、8为例来具体看看数据和计算特征。步骤7耗时49分钟，主要SQL语句，是用小表过滤大表，还有其他条件过滤。脱敏后的SQL语句如下： select B_NO, CUST_NO, BAL, nvl(nullif(a.D_BILL, ''), a.BILL_NO), BEGIN_DATE, END_DATE from BOOK_ HIS a WHERE CORP = v_corp and v_sDate >= BEGIN_DATE and v_eDate <= END_DATE and not exists( select 1 from SESSION.TMP_BILL d WHERE nvl(nullif(a.D_BILL, ''), a.BILL_NO)=d.R_NO ) 贷款台账信息历史表BOOK_HIS数据量是1.1亿条，有78个字段，参与计算的有7个字段，主键是CORP，D_BILL，BEGIN_DATE，END_DATE。
SESSION.TMP_W_BILL是前序步骤生成的临时表，是由企业贷款本金表D_HIS_P经过条件过滤得到，数据量约244万条。步骤7中，要用TMP_BILL表中的借据号过滤BOOK_HIS表，计算结果保存为临时表，并且要按照CUST_NO和R_NO分别建索引，为下一个步骤做准备。 步骤8耗时36分钟，是用两个小表来过滤步骤7的结果临时表，过滤之后再按照RC_NO分组汇总。 SELECT LN_R_NO, SUM(CASE WHEN v_sDate_LM BETWEEN BEGIN_DATE AND END_DATE THEN BAL END) BAL_LM, … SUM(CASE WHEN v_sDate_LY BETWEEN BEGIN_DATE AND END_DATE THEN BAL ELSE 0 END) BAL_LYY FROM session.BOOK_HIS_TEMP a WHERE ( exists(select 1 from CUST b where a.CUST_NO=b.CUST_NO and b.CORP=v_corp and v_sDate BETWEEN b.BEGIN_DATE AND b.END_DATE) or exists(select 1 from session.SALE_BILL c WHERE a.R_NO=c.R_NO) ) GROUP BY R_NO 对公客户基本信息历史表CUST，共57万条数据，session.SALE_BILL 是从ACC表（3.2万条数据）过滤而来。两个表数据量都不是很大。
跑批服务器是16核CPU，内存32G。数据每天通过ETL从生产数据库增量更新到DB2数据库中。 第二，确定优化思路。 服务器内存不太大，贷款台账信息历史表BOOK_HIS这样的大表无法全部装入内存，因此采用大表外存、小表内存的计算方式。
步骤7中大表BOOK_HIS要和小表做关联过滤。其结果还要在步骤8中和两个小表做过滤。两个步骤的三个小表都可以全内存，因此要考虑对大表建立游标，一次遍历完成条件过滤、三个小表的关联过滤和最后的分组汇总。这样，可以省去中间结果写硬盘的时间和建立索引的时间。 大表BOOK_HIS要按照CORP，BEGIN_DATE，END_DATE过滤，可以考虑将大表按照CORP， BEGIN_DATE，END_DATE有序存放，这样可以快速过滤，找到需要的数据。 大表BOOK_HIS与小表SESSION.TMP_BILL做not exists关联计算。关联字段是大表两个字段的表达式，要在遍历大表的时候，先计算表达式，然后直接做关联过滤。 大表BOOK_HIS与两个小表cust和session.SALE_BILL分别做关联过滤，结果需要去重合并，因此要采取大表一次遍历，分批在内存中计算两种关联过滤，结果合并的方式，仍可以保证只对大表遍历一次。这种算法，我们称为遍历复用。 最后的分组汇总结果也不大，可以采用在内存中向结果集聚合的方式计算。 大表BOOK_HIS有78个字段，参与计算的有7个字段；小表参与计算的字段都只有一个。因此适合列存，可以减少硬盘读取的数据量，提高性能。 两个步骤合并之后，对大表一次遍历，可以采用多线程并行计算，充分发挥CPU16核的计算能力，提高计算速度。 第三，确定技术选型和实施方案。 关系数据库建立在无序集合理论的基础上，没有办法实现大表BOOK_HIS在物理上有序存放。 另外，如果将步骤7、8两个主要SQL合并成一个SQL，会变得更复杂，数据库自动化优化机制很难达到最佳的查询路径。实际测试也证明，多临时表关联性能会急剧下降，所以整个存储过程才被重构拆分为目前的很多个SQL。也就是说，上面的优化方案没有办法在关系数据库里用SQL实现了。 如果不用关系数据库，那将数据外置到文件来自行处理会更方便一些。服务器内存不大，无法装入全部数据，也无法实施全内存计算技术（包括某些被优化过的内存数据库）。 跑批用到的新增数据每天从生产库导出到跑批库，数据本来就要移动，我们可以在移动过程中同时将数据写出到文件，以实现上述的高性能算法。文件系统的IO性能更好，写入到文件会比写入到数据库更快，数据外置在工程上也是可行的。 基于文件，再使用Java或C++等高级语言可以实现上述算法，但编码量过大，实现周期过长，容易出现代码错误隐患，也很难调试和维护。 开源集算器的SPL语言提供上述所有的算法支持，包括高性能列存文件、文件游标、多线程并行、小结果内存分组、游标复用等机制，能够让我们用较少的代码量快速实现这种个性化的计算。 第四，实现实施方案。 先要将存储过程用到的大表BOOK_HIS导出为 SPL 列存组表文件，第一次导出时间较长，以后每天增量导出、有序归并，时间就较短了。实际测试发现，新增数据137万，有序归并的时间是5分钟。用到的小表可以每天全量导出，也可以采取增量导出方式。 然后编写SPL代码，实现原存储过程的计算需求。对公贷款协议明细存储过程只是跑批任务很多存储过程的一个，和其他存储过程有先后关系。因此，要采取旁路加速的方式，计算结果还要写回数据库。如下图： 集算器SPL支持命令行调用，DB2数据库的跑批存储过程可以通过命令行调用已经写好的SPL代码。SPL 也支持JDBC接口，可以用第三方调度工具调用。
进过几周时间的编程调试，对公贷款协议明细存储过程的优化完成，效果明显。使用相同的硬件，优化前运行时间是2个小时，优化后仅需要10分钟就可以完成计算，性能提高12倍。 在编程难度方面，SPL做了大量封装，提供了丰富的函数，内置了上述优化方案需要的基本算法和存储机制。上面描述的算法比较复杂，但实际编写的代码并不长，和原来的SQL相比少很多，开发效率很高。原存储过程3300多行，用SPL改写后，仅约500格SPL语句即可实现，代码量减少了6倍多，这会极大的提升易维护度。
解决性能优化难题，最重要的是设计出高性能的计算方案，有效降低计算复杂度，最终把速度提上去。因此，一方面要充分理解计算和数据的特征，另一方面也要熟知常见的高性能算法，才能因地制宜地设计出合理的优化方案。本次工作中用到的基本高性能算法，都可以从下面这门课程中找到： 点击这里学习性能优化课程（底部原文中可点击链接） ，有兴趣的同学可以参考。 很遗憾的是，当前业界主流大数据体系仍以关系数据库为基础，无论是传统的MPP还是HADOOP体系以及新的一些技术，都在努力将编程接口向SQL靠拢。兼容SQL确实能让用户更容易上手，但受制于理论限制的SQL却无法实现大多数高性能算法，眼睁睁地看着硬件资源被浪费，还没有办法改进。SQL不应是大数据计算的未来。 有了优化方案后，还要用好的程序语言来高效地实现这个算法。虽然常见的高级语言能够实现大多数优化算法，但代码过于冗长，开发效率过低，会严重影响程序的可维护性。集算器SPL是个很好的选择，它有足够的算法底层支持，代码能做到很简洁，还提供了友好的可视化调试机制，能有效提高开发效率，以及降低维护成本。
重磅！开源SPL交流群成立了
简单好用的SPL开源啦！
为了给感兴趣的小伙伴们提供一个相互交流的平台，
特地开通了交流群（群完全免费，不广告不卖课）
需要进群的朋友，可长按扫描下方二维码